# -*- coding: utf-8 -*-

import pymysql
import settings
import os

"""
MYSQL数据库操作类
"""
class MysqlClient:

    #写入文件
    def save_file(self, fileName, dataList):
        f = "d:\\data\\mysql\\%s.sql" %(fileName)
        try:
            #只需要将之前的”w"改为“a"即可，代表追加内容
            with open(f, "a" ,encoding="utf-8") as file:
                for json in dataList:
                    file.write(str(json) + "\n")
        except Exception as e:
            print(e)

        return True

    #读取异常Sql文件
    def readErrorList(self, fileName):
        path = "d:\\data\\mysql\\%s.sql" %(fileName)
        lines = []
        try:
            size = os.path.getsize(path)
            if size > 0:
                with open(path, "r" ,encoding="utf-8") as file:
                    lines = file.readlines()
        except Exception as e:
            print(e)

        return lines

    #批量执行SQL
    def execute_sql(self, connect, cursor, fileName, sqlList):
        connect.ping(reconnect = True)
        sql_errors = []
        count = 0
        for sql in sqlList :
            try:
                ret = cursor.execute(sql)
                count += ret
            except Exception as e:
                print(e)
                sql_errors.append(sql)
        # 提交，不进行提交无法保存到数据库
        connect.commit()
        #connect.close()
        #将异常的sql保存下来
        if len(sql_errors) > 0 :
            self.save_file(fileName, sql_errors)
            print("@@@插入失败数：", len(sql_errors))

        return count

    #单行插入返回ID
    def insert_one(self, connect, cursor, insert_sql, insert_data):
        id = 0
        # 批量插入
        try:
            connect.ping(reconnect = True)
            cursor.execute(
                insert_sql, insert_data
            )
            # 最后插入行的主键id，使用 cursor.lastrowid 和 connect.insert_id() 时一定要在 conn.commit() 之前
            id = cursor.lastrowid
            #print(connect.insert_id())

            # 提交，不进行提交无法保存到数据库
            connect.commit()
        except Exception as e:
            print(e)
            connect.rollback()
        #finally:
        #connect.close()

        return id

    #多行插入
    def insert_many(self, connect, cursor, insert_sql, insert_data):
        connect.ping(reconnect = True)

        # 批量插入
        try:
            res = cursor.executemany(insert_sql, insert_data)
            print("res = ", res)
            # 提交，不进行提交无法保存到数据库
            connect.commit()
        except Exception as e:
            print(e)
            connect.rollback()
        #finally:
        #connect.close()

        return True

    #统计记录数
    def count_data(self, connect, cursor, query_sql):
        connect.ping(reconnect = True)
        try:
            cursor.execute(query_sql)
            queryResult = cursor.fetchone()

        except Exception as e:
            print(e)
        #finally:

        return queryResult

    #查询数据
    def query_data(self, connect, cursor, query_sql):
        connect.ping(reconnect = True)
        try:
            cursor.execute(query_sql)
            queryResult = cursor.fetchall()

        except Exception as e:
            print(e)
        #finally:

        return queryResult

    def conn_odsdb(self):
        #connect = pymysql.connect(host='172.28.56.90', user='mysql', password='egSQ7HhxajHZjvdX', port=3306, database='odsdb',charset="utf8")
        connect = pymysql.connect(
            host=settings.MYSQL_CONN_ODSDB["HOST"],
            user=settings.MYSQL_CONN_ODSDB["USER"],
            passwd=settings.MYSQL_CONN_ODSDB["PASSWD"],
            port=settings.MYSQL_CONN_ODSDB["PORT"],
            db=settings.MYSQL_CONN_ODSDB["DB"],
            charset=settings.MYSQL_CONN_ODSDB["CHARSET"],
        )
        cursor = connect.cursor()
        print("链接数据库odsdb成功")
        return connect, cursor

    def conn_istorage(self):
        connect = pymysql.connect(
            host=settings.MYSQL_CONN_ISTORAGE["HOST"],
            user=settings.MYSQL_CONN_ISTORAGE["USER"],
            passwd=settings.MYSQL_CONN_ISTORAGE["PASSWD"],
            port=settings.MYSQL_CONN_ISTORAGE["PORT"],
            db=settings.MYSQL_CONN_ISTORAGE["DB"],
            charset=settings.MYSQL_CONN_ISTORAGE["CHARSET"],
        )
        cursor = connect.cursor()
        print("链接数据库odsdb成功")
        return connect, cursor


    